import {
  Provide,
  Scope,
  ScopeEnum,
  Init,
  Inject,
  Config,
  MidwayError,
  App,
  IMidwayApplication,
  safeRequire,
  MidwayConfigService,
  getClassMetadata,
  CONTROLLER_KEY,
  listModule,
} from '@midwayjs/core';
import { BaseService } from '@cool-midway/core';
import { InjectDataSource } from '@midwayjs/typeorm';
import * as fs from 'fs';
import * as path from 'path';
import { DataSource, EntityMetadata } from 'typeorm';

/**
 * 存储过程生成器
 */
@Provide()
@Scope(ScopeEnum.Singleton)
export class ProcedureGenerator extends BaseService {
  @InjectDataSource()
  dataSource: DataSource;

  @Config('typeorm')
  typeormConfig;

  @App()
  app: IMidwayApplication;

  @Inject()
  configService: MidwayConfigService; // MidwayConfigService

  // 存储过程模板缓存
  private procedureTemplates: Map<string, string> = new Map();

  constructor() {
    super();
    console.log('\n=== ProcedureGenerator 构造函数被调用 ===');
  }

  @Init()
  async init() {
    try {
      console.log('\n=== 开始初始化存储过程生成器 ===');
      // console.log('检查 this.dataSource 的值:', this.dataSource);

      if (this.dataSource && !this.dataSource.isInitialized) {
        await this.dataSource.initialize();
      } else if (!this.dataSource) {
        console.error("错误：DataSource 未被注入到 ProcedureGenerator！");
        return;
      }

      // 获取所有控制器
      const controllers = listModule(CONTROLLER_KEY);
      console.log(`共找到 ${controllers.length} 个控制器`);

      // 遍历所有控制器
      for (const controllerClass of controllers) {
        try {
          // 获取控制器元数据
          const metadata = getClassMetadata(CONTROLLER_KEY, controllerClass);
          if (!metadata) {
            continue;
          }
          // console.log('metadata', metadata);

          // 获取模块名
          const moduleName = metadata.module;
          if (!moduleName) {
            // console.log(
            //   `控制器 ${controllerClass.name} 未配置模块名，跳过处理`
            // );
            continue;
          }

          // console.log(
          //   `处理控制器 ${controllerClass.name}，所属模块: ${moduleName}`
          // );

          try {
            // 获取模块配置
            const config = require(path.resolve(
              __dirname,
              `../../modules/${moduleName}/config`
            )).default();

            // console.log('config', config);

            // 获取实体
            const entity = metadata.curdOption?.entity;
            if (!entity) {
              // console.log(
              //   `控制器 ${controllerClass.name} 未配置实体，跳过处理`
              // );
              continue;
            }

            const entityMetadata = this.dataSource.getMetadata(entity);

            // 检查是否启用了存储过程
            if (config?.procedure?.enable) {
              const types = config.procedure?.types || [
                'add',
                'delete',
                'update',
                'info',
                'list',
                'page',
              ];
              const once = config.procedure?.once || false;

              console.log(`模块 ${moduleName} 的存储过程配置:`, {
                types,
                once,
              });

              // 如果开启了 once 选项，先检查是否存在存储过程
              if (once) {
                const existingProcedures = await this.checkExistingProcedures(
                  entityMetadata.tableName,
                  types
                );
                if (existingProcedures.length > 0) {
                  console.log(
                    `跳过 ${
                      entityMetadata.name
                    } 的存储过程生成，因为以下存储过程已存在: ${existingProcedures.join(
                      ', '
                    )}`
                  );
                  continue;
                }
              }

              await this.generateAndSyncProcedures(entityMetadata, types);
              // console.log(`${entityMetadata.name} 模块存储过程初始化完成`);
            } else {
              // console.log(`模块 ${moduleName} 未启用存储过程生成`);
            }
          } catch (error) {
            console.log('error', error);
            if (error.code === 'MODULE_NOT_FOUND') {
              console.log(`模块 ${moduleName} 的配置文件不存在，跳过处理`);
              continue;
            }
            console.error(`处理模块 ${moduleName} 失败:`, error);
          }
        } catch (error) {
          console.error(`处理控制器失败:`, error);
        }
      }

      console.log('=== 存储过程生成器初始化完成 ===\n');
    } catch (error) {
      console.error('存储过程生成器初始化失败:', error);
    }
  }

  /**
   * 同步所有存储过程 (已废弃，改为按需同步)
   * @deprecated 使用 generateAndSyncProcedures 代替
   */
  async syncAllProcedures() {
    console.warn(
      '警告: syncAllProcedures 方法已废弃，请使用 generateAndSyncProcedures 方法按需同步特定实体的存储过程'
    );
  }

  /**
   * 为指定实体生成并同步存储过程
   */
  async generateAndSyncProcedures(
    entity: EntityMetadata,
    procedures: ('add' | 'delete' | 'update' | 'info' | 'list' | 'page')[] = [
      'add',
      'delete',
      'update',
      'info',
      'list',
      'page',
    ]
  ) {
    console.log(`开始为实体 ${entity.name} 生成存储过程...`);
    console.log(`需要生成的存储过程: ${procedures.join(', ')}`);

    const tableName = entity.tableName;
    console.log(`对应的数据库表名: ${tableName}`);

    // 根据配置生成指定的存储过程
    const generatedProcedures: Record<string, string> = {};

    for (const proc of procedures) {
      console.log(`正在生成 ${proc} 存储过程...`);
      switch (proc) {
        case 'page':
          generatedProcedures.page = this.generatePageProcedure(entity);
          break;
        case 'list':
          generatedProcedures.list = this.generateListProcedure(entity);
          break;
        case 'info':
          generatedProcedures.info = this.generateInfoProcedure(entity);
          break;
        case 'add':
          generatedProcedures.add = this.generateAddProcedure(entity);
          break;
        case 'update':
          generatedProcedures.update = this.generateUpdateProcedure(entity);
          break;
        case 'delete':
          generatedProcedures.delete = this.generateDeleteProcedure(entity);
          break;
      }
      console.log(`${proc} 存储过程生成完成`);
    }

    // 保存到文件
    console.log('正在保存存储过程到文件...');
    await this.saveProceduresToFile(entity.name, generatedProcedures);
    console.log('存储过程已保存到文件');

    // 同步到数据库
    console.log('开始同步存储过程到数据库...');
    await this.syncProceduresToDB(generatedProcedures);
  }

  /**
   * 同步存储过程到数据库
   */
  private async syncProceduresToDB(procedures: Record<string, string>) {
    console.log('开始同步存储过程到数据库...');
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();

    try {
      // 开启事务
      await queryRunner.startTransaction();
      console.log(`共有 ${Object.keys(procedures).length} 个存储过程需要同步`);

      // 执行每个存储过程的创建
      for (const [key, procedure] of Object.entries(procedures)) {
        if (!procedure?.trim()) continue;

        // 解析存储过程名称
        const matches = procedure.match(/CREATE PROCEDURE ([^\s(]+)/);
        if (!matches) {
          console.warn(`无法解析存储过程名称: ${key}`);
          continue;
        }
        const procName = matches[1];

        try {
          // 1. 先删除已存在的存储过程
          await queryRunner.query(`DROP PROCEDURE IF EXISTS ${procName}`);
          console.log(`已删除旧的存储过程: ${procName}`);

          // 2. 提取CREATE PROCEDURE部分
          const createPart = procedure.substring(
            procedure.indexOf('CREATE PROCEDURE')
          );

          // 3. 创建新的存储过程
          await queryRunner.query(createPart);
          console.log(`成功创建存储过程: ${procName}`);
        } catch (error) {
          console.error(`创建存储过程 ${procName} 失败:`, error);
          throw error;
        }
      }

      // 提交事务
      await queryRunner.commitTransaction();
      console.log('所有存储过程同步完成');
    } catch (err) {
      // 回滚事务
      await queryRunner.rollbackTransaction();
      console.error('同步存储过程失败:', err);
      throw err;
    } finally {
      // 释放连接
      await queryRunner.release();
    }
  }

  /**
   * 生成新增存储过程
   */
  private generateAddProcedure(entity: EntityMetadata): string {
    const tableName = entity.tableName;
    const columns = entity.columns
      .filter(col => !col.isVirtual && !col.isGenerated)
      .map(col => col.databaseName);

    return `DROP PROCEDURE IF EXISTS sp_${tableName}_add;

CREATE PROCEDURE sp_${tableName}_add(
  ${columns
    .map(col => `IN p_${col} ${this.getColumnType(entity, col)}`)
    .join(',\n  ')}
)
BEGIN
  INSERT INTO ${tableName} (${columns.join(', ')})
  VALUES (${columns.map(col => `p_${col}`).join(', ')});
  
  SELECT LAST_INSERT_ID() as id;
END`;
  }

  /**
   * 生成更新存储过程
   */
  private generateUpdateProcedure(entity: EntityMetadata): string {
    const tableName = entity.tableName;
    const columns = entity.columns
      .filter(
        col => !col.isVirtual && !col.isGenerated && col.databaseName !== 'id'
      )
      .map(col => col.databaseName);

    return `DROP PROCEDURE IF EXISTS sp_${tableName}_update;

CREATE PROCEDURE sp_${tableName}_update(
  IN p_id INT,
  ${columns
    .map(col => `IN p_${col} ${this.getColumnType(entity, col)}`)
    .join(',\n  ')}
)
BEGIN
  UPDATE ${tableName} 
  SET ${columns.map(col => `${col} = p_${col}`).join(',\n      ')}
  WHERE id = p_id;
  
  SELECT p_id as id;
END;`;
  }

  /**
   * 生成删除存储过程
   */
  private generateDeleteProcedure(entity: EntityMetadata): string {
    const tableName = entity.tableName;
    return `DROP PROCEDURE IF EXISTS sp_${tableName}_delete;

CREATE PROCEDURE sp_${tableName}_delete(
  IN p_id INT
)
BEGIN
  DELETE FROM ${tableName} WHERE id = p_id;
END;`;
  }

  /**
   * 生成详情查询存储过程
   */
  private generateInfoProcedure(entity: EntityMetadata): string {
    const tableName = entity.tableName;
    return `DROP PROCEDURE IF EXISTS sp_${tableName}_info;

CREATE PROCEDURE sp_${tableName}_info(
  IN p_id INT
)
BEGIN
  SELECT * FROM ${tableName} WHERE id = p_id;
END;`;
  }

  /**
   * 生成列表查询存储过程
   */
  private generateListProcedure(entity: EntityMetadata): string {
    const tableName = entity.tableName;
    const columns = entity.columns
      .filter(col => !col.isVirtual)
      .map(col => col.databaseName);

    // 生成参数声明，不使用 DEFAULT NULL
    const paramDeclarations = columns
      .map(col => `IN p_${col} ${this.getColumnType(entity, col)}`)
      .join(',\n  ');

    // 生成 WHERE 条件，使用 OR 关系
    const whereConditions = columns
      .map(col => `(p_${col} IS NOT NULL AND ${col} = p_${col})`)
      .join(' OR ');

    return `DROP PROCEDURE IF EXISTS sp_${tableName}_list;

CREATE PROCEDURE sp_${tableName}_list(
  ${paramDeclarations}
)
BEGIN
  SELECT * FROM ${tableName} 
  WHERE (${whereConditions}) OR (${columns
      .map(col => `p_${col} IS NULL`)
      .join(' AND ')})
  ORDER BY id DESC;
END;`;
  }

  /**
   * 生成分页查询存储过程
   */
  private generatePageProcedure(entity: EntityMetadata): string {
    const tableName = entity.tableName;

    return `DROP PROCEDURE IF EXISTS sp_${tableName}_page;

CREATE PROCEDURE sp_${tableName}_page(
  IN p_sort VARCHAR(10),
  IN p_order VARCHAR(255),
  IN p_page INT,
  IN p_size INT
)
BEGIN
  DECLARE offset INT;
  DECLARE orderBy VARCHAR(512);
  
  -- 检查排序字段是否为空
  IF p_order IS NULL OR p_order = '' THEN
    SET p_order = 'id';
  END IF;
  
  -- 检查排序方式是否为空
  IF p_sort IS NULL OR p_sort = '' THEN
    SET p_sort = 'desc';
  END IF;
  
  -- 计算偏移量
  SET offset = (p_page - 1) * p_size;
  
  -- 构建排序语句
  SET orderBy = CONCAT(' ORDER BY ', p_order, ' ', p_sort);
  
  -- 构建并执行查询
  SET @sql = CONCAT('SELECT * FROM ', '${tableName}', orderBy, ' LIMIT ?, ?');
  SET @offset = offset;
  SET @size = p_size;
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt USING @offset, @size;
  DEALLOCATE PREPARE stmt;
  
  -- 返回总数
  SELECT COUNT(*) as total FROM ${tableName};
END;`;
  }

  /**
   * 获取列的SQL类型
   */
  private getColumnType(entity: EntityMetadata, columnName: string): string {
    const column = entity.columns.find(col => col.databaseName === columnName);
    if (!column) return 'VARCHAR(255)';

    // 获取数据库字段类型
    const dbType = column.type?.toString()?.toLowerCase() || '';

    // 处理特殊类型
    if (typeof column.type === 'function') {
      const typeName = column.type.name?.toLowerCase() || '';
      switch (typeName) {
        case 'number':
          // 检查是否为浮点数类型
          return column.type === Number &&
            (column.precision !== undefined || column.scale !== undefined)
            ? 'DECIMAL(10,2)'
            : 'INT';
        case 'string':
          return `VARCHAR(${column.length || 255})`;
        case 'boolean':
          return 'TINYINT(1)';
        case 'date':
          return 'DATETIME';
        default:
          return 'VARCHAR(255)';
      }
    }

    // 处理普通类型
    switch (dbType) {
      case 'float':
      case 'double':
      case 'decimal':
        return 'DECIMAL(10,2)';
      case 'int':
      case 'integer':
      case 'smallint':
      case 'bigint':
        return 'INT';
      case 'tinyint':
        return 'TINYINT(1)';
      case 'varchar':
      case 'char':
        return `VARCHAR(${column.length || 255})`;
      case 'text':
        return 'TEXT';
      case 'datetime':
      case 'timestamp':
        return 'DATETIME';
      case 'date':
        return 'DATE';
      case 'time':
        return 'TIME';
      case 'boolean':
        return 'TINYINT(1)';
      default:
        return 'VARCHAR(255)';
    }
  }

  /**
   * 保存存储过程到文件
   */
  private async saveProceduresToFile(
    entityName: string,
    procedures: Record<string, string>
  ) {
    const dir = path.join(
      path.dirname(this.app.getAppDir()),
      'src/sql/procedures'
    );
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir, { recursive: true });
    }

    const content = Object.values(procedures).join('\n\n');
    const filePath = path.join(dir, `${entityName}.sql`);
    fs.writeFileSync(filePath, content, 'utf8');
  }

  /**
   * 检查存储过程是否已存在
   */
  private async checkExistingProcedures(
    tableName: string,
    types: string[]
  ): Promise<string[]> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();

    try {
      const existingProcedures: string[] = [];
      const database = this.dataSource.options.database as string;

      for (const type of types) {
        const procName = `sp_${tableName}_${type}`;
        const sql = `SELECT COUNT(*) as count 
           FROM information_schema.routines 
           WHERE routine_schema = ? AND routine_name = ? AND routine_type = 'PROCEDURE'`;
        const params = [database, procName];

        const result = await queryRunner.query(sql, params);

        if (result[0].count > 0) {
          existingProcedures.push(procName);
        }
      }

      return existingProcedures;
    } catch (error) {
      console.error('检查存储过程失败:', error);
      throw error;
    } finally {
      await queryRunner.release();
    }
  }
}
